﻿using System;
using System.Data;
using System.Collections.Generic;
using CommonType;

enum type { ADMIN, USER, GUEST };//用户类型

/// <summary>
/// 数据访问层
/// -designed by Hom
/// </summary>
/// 
namespace DAL
{
    /// <summary>
    ///用户信息类
    /// </summary>
    public class User
    {
        
        public UserInfo GetInfo(long ID)
        {
            UserInfo ui = new UserInfo();
            string strTb = "UserInfo";
            string strSql = "select * from " + strTb + " where ID=" + ID.ToString();
            DataSet ds = CC.CommonDataClass.GetQueryDataSet(strSql, strTb);

            ui.ID = Convert.ToInt32(ds.Tables[0].Rows[0]["ID"]);
            ui.userName = Convert.ToString(ds.Tables[0].Rows[0]["userName"]);
            ui.nickName = Convert.ToString(ds.Tables[0].Rows[0]["nickName"]);
            ui.pass = Convert.ToString(ds.Tables[0].Rows[0]["pass"]);
            ui.registerTime = Convert.ToDateTime(ds.Tables[0].Rows[0]["registerTime"]);
            ui.publishedNewsID = Convert.ToInt32(ds.Tables[0].Rows[0]["publishedNewsID"]);
            ui.publishedCommentID = Convert.ToInt32(ds.Tables[0].Rows[0]["publishedCommentID"]);
            ui.publishedMessageID = Convert.ToInt32(ds.Tables[0].Rows[0]["publishedMessageID"]); 
            ui.qq = Convert.ToInt32(ds.Tables[0].Rows[0]["qq"]);
            ui.msn = Convert.ToString(ds.Tables[0].Rows[0]["msn"]);
            ui.email = Convert.ToString(ds.Tables[0].Rows[0]["email"]);
            ui.userType = Convert.ToInt32(ds.Tables[0].Rows[0]["type"]);
            ui.icon = Convert.ToString(ds.Tables[0].Rows[0]["icon"]);

            return ui;
        }
        
        public bool Add(UserInfo ui)
        {
            string strTb = "UserInfo";
            string strSql = "insert into [" + strTb + "] (userName,nickName,pass,registerTime,publishedNewsID,publishedCommentID,publishedMessageID,qq,msn,email,type,icon) values('" + ui.userName + "','" + ui.nickName + "','" 
                + ui.pass + "',#" + DateTime.Now + "#," + ui.publishedNewsID.ToString() + "," + ui.publishedCommentID.ToString() + ","
                + ui.publishedMessageID.ToString() + "," + ui.qq.ToString() + ",'" + ui.msn + "','" + ui.email + "',"
                + ui.userType.ToString() + ",'" + ui.icon+"')";
            return CC.CommonDataClass.UseQueryCommand(strSql);
        }
        
        public bool Edit(long ID, UserInfo ui)
        {
            string strTb = "UserInfo";
            string strSql = "update " + strTb + " set [nickName]='" + ui.nickName + "',[pass]='" + ui.pass + "',[qq]=" + ui.qq.ToString() + ",[msn]='"
                + ui.msn + "',[email]='" + ui.email + "',[type]=" + ui.userType.ToString() + ",[icon]='" + ui.icon + "' where [ID]=" + ID.ToString();
            return CC.CommonDataClass.UseQueryCommand(strSql);
        }

        public bool Delete(int ID)
        {
            string strTb = "UserInfo";
            string strSql = "delete from " + strTb + " where ID=" + ID.ToString();
            return CC.CommonDataClass.UseQueryCommand(strSql);
        }
    }

    /// <summary>
    /// 新闻信息类
    /// </summary>
    
    
    public class News
    {
        
        public bool Add(ArticalPiece ap)  //添加一条新闻
        {
            string strTb = "ArticalPiece";
            string strSql = "insert into " + strTb + " values(" + ap.ID.ToString() + ",'" + ap.title + "','" + ap.authorID + "',#"
                + ap.publishTime + "#,'" + ap.publisher + "','" + ap.content + "'," + ap.numView.ToString() + ")";
            return CC.CommonDataClass.UseQueryCommand(strSql);
        }
        public bool Edit(int ID, ArticalPiece ap)   //编辑一条新闻
        {
            string strTb = "ArticalPiece";
            string strSql = "update " + strTb + " set title='" + ap.title + "',authorID='" + ap.authorID + "',publishTime=#" 
                + ap.publishTime + "#,publisher='" + ap.publisher + "',content='" + ap.content + "',numView=" 
                + ap.numView.ToString() + " where ID=" + ID.ToString();
            return CC.CommonDataClass.UseQueryCommand(strSql);
        }
        //public bool SuperEdit(int ID, string field, object content); //可以先不写
        public bool Delete(int ID)   //删除一条新闻
        {
            string strTb = "ArticalPiece";
            string strSql = "delete from " + strTb + " where ID=" + ID.ToString();
            return CC.CommonDataClass.UseQueryCommand(strSql);
        }
        
        public List<ArticalPiece> GetLatest(int num)   //获取最近n条新闻
        {
            List<ArticalPiece> lap = new List<ArticalPiece>();
            string strTb = "ArticalPiece";
            string strSql = "select top "+num.ToString()+" * from "+strTb+" where publishTime order by publishTime";
            DataSet ds = CC.CommonDataClass.GetQueryDataSet(strSql, strTb);
            ArticalPiece ap = new ArticalPiece();
            for (int i = 0; i < num; i++)
            {
                ap.ID = Convert.ToInt32(ds.Tables[0].Rows[i]["ID"]);
                ap.title = Convert.ToString(ds.Tables[0].Rows[i]["title"]);
                ap.authorID = Convert.ToString(ds.Tables[0].Rows[i]["authorID"]);
                ap.publishTime = Convert.ToDateTime(ds.Tables[0].Rows[i]["publishTime"]);
                ap.publisher = Convert.ToString(ds.Tables[0].Rows[i]["publisher"]);
                ap.content = Convert.ToString(ds.Tables[0].Rows[i]["content"]);
                ap.numView = Convert.ToInt32(ds.Tables[0].Rows[i]["numView"]);
                lap.Add(ap);
            }
            return lap;
        }

        public ArticalPiece Get(int ID)   //由ID获得新闻
        {
            ArticalPiece ap = new ArticalPiece();
            string strTb = "ArticalPiece";
            string strSql = "select * from " + strTb + " where ID=" + ID.ToString();
            DataSet ds = CC.CommonDataClass.GetQueryDataSet(strSql, strTb);

            ap.ID = Convert.ToInt32(ds.Tables[0].Rows[0]["ID"]);
            ap.title = Convert.ToString(ds.Tables[0].Rows[0]["title"]);
            ap.authorID = Convert.ToString(ds.Tables[0].Rows[0]["authorID"]);
            ap.publishTime = Convert.ToDateTime(ds.Tables[0].Rows[0]["publishTime"]);
            ap.publisher = Convert.ToString(ds.Tables[0].Rows[0]["publisher"]);
            ap.content = Convert.ToString(ds.Tables[0].Rows[0]["content"]);
            ap.numView = Convert.ToInt32(ds.Tables[0].Rows[0]["numView"]);

            return ap;
        }
    }
    
    /// <summary>
    /// 评论信息类
    /// </summary>
    
    public class Comment
    {
        
        public bool Add(CommentPiece cp)
        {
            string strTb = "CommentPiece";
            string strSql = "insert into " + strTb + "(newsID,authorID,publishTime,content) values(" + cp.newsID.ToString() +",'" + cp.authorID + "',#"
                + cp.publishTime +"#,'" + cp.content + "')";
            return CC.CommonDataClass.UseQueryCommand(strSql);
        }
        public bool Edit(int ID, CommentPiece cp)
        {
            string strTb = "CommentPiece";
            string strSql = "update " + strTb + " set newsID=" + cp.newsID.ToString() + ",authorID='" + cp.authorID + "',publishTime=#"
                + cp.publishTime + "#,content='" + cp.content + "' where ID=" + ID.ToString();
            return CC.CommonDataClass.UseQueryCommand(strSql);
        }
        public bool Delete(int ID)
        {
            string strTb = "CommentPiece";
            string strSql = "delete from " + strTb + " where ID=" + ID.ToString();
            return CC.CommonDataClass.UseQueryCommand(strSql);
        }
        public List<CommentPiece> GetLatest(int num)
        {
            List<CommentPiece> lcp = new List<CommentPiece>();
            string strTb = "CommentPiece";
            string strSql = "select top " + num.ToString() + " * from " + strTb + " where publishTime order by publishTime";
            DataSet ds = CC.CommonDataClass.GetQueryDataSet(strSql, strTb);
            CommentPiece cp = new CommentPiece();
            for (int i = 0; i < num; i++)
            {
                cp.ID = Convert.ToInt32(ds.Tables[0].Rows[i]["ID"]);
                cp.newsID = Convert.ToInt32(ds.Tables[0].Rows[i]["newsID"]);
                cp.authorID = Convert.ToString(ds.Tables[0].Rows[i]["authorID"]);
                cp.publishTime = Convert.ToDateTime(ds.Tables[0].Rows[i]["publishTime"]);
                cp.content = Convert.ToString(ds.Tables[0].Rows[i]["content"]);
                lcp.Add(cp);
            }
            return lcp;
        }
        public CommentPiece Get(int ID)
        {
            string strTb = "CommentPiece";
            string strSql = "select * from " + strTb + " where ID=" + ID.ToString();
            CommentPiece cp = new CommentPiece();
            DataSet ds = CC.CommonDataClass.GetQueryDataSet(strSql, strTb);

            cp.ID = Convert.ToInt32(ds.Tables[0].Rows[0]["ID"]);
            cp.newsID = Convert.ToInt32(ds.Tables[0].Rows[0]["newsID"]);
            cp.authorID = Convert.ToString(ds.Tables[0].Rows[0]["authorID"]);
            cp.publishTime = Convert.ToDateTime(ds.Tables[0].Rows[0]["publishTime"]);
            cp.content = Convert.ToString(ds.Tables[0].Rows[0]["content"]);

            return cp;
        }
    }

    /// <summary>
    /// 消息类
    /// </summary>

    public class Message
    {
        
        public bool Add(MessagePiece mp)
        {
            string strTb = "MessagePiece";
            string strSql = "insert into "+strTb+" (authorID,publishTime,content) values('"+mp.authorID+"',#"+DateTime.Now+"#,'"+mp.content+"')";
            return CC.CommonDataClass.UseQueryCommand(strSql);
        }
        public bool Edit(int ID, MessagePiece mp)
        {
            string strTb = "MessagePiece";
            string strSql = "update "+strTb+" set authorID='"+mp.authorID+"',publishTime=#"+mp.publishTime+"#,content='"
                +mp.content+"' where ID="+ID.ToString();
            return CC.CommonDataClass.UseQueryCommand(strSql);
        }
        public bool Delete(int ID)
        {
            string strTb = "MessagePiece";
            string strSql = "delete from "+strTb+" where ID="+ID.ToString();
            return CC.CommonDataClass.UseQueryCommand(strSql);
        }
        public List<MessagePiece> GetComment(int num)
        {
            List<MessagePiece> lmp = new List<MessagePiece>();
            string strTb = "MessagePiece";
            string strSql = "select top " + num.ToString() + " * from " + strTb + " where publishTime order by publishTime";
            DataSet ds = CC.CommonDataClass.GetQueryDataSet(strSql, strTb);
            MessagePiece mp = new MessagePiece ();
            for (int i = 0; i < num; i++)
            {
                mp.ID = Convert.ToInt32(ds.Tables[0].Rows[i]["ID"]);
                mp.authorID = Convert.ToString(ds.Tables[0].Rows[i]["authorID"]);
                mp.publishTime = Convert.ToDateTime(ds.Tables[0].Rows[i]["publishTime"]);
                mp.content = Convert.ToString(ds.Tables[0].Rows[i]["content"]);
                lmp.Add(mp);
            }
            return lmp;
        }
        public MessagePiece Get(int ID)
        {
            string strTb = "MessagePiece";
            string strSql = "select * from " + strTb + " where ID=" + ID.ToString();
            MessagePiece mp = new MessagePiece();
            DataSet ds = CC.CommonDataClass.GetQueryDataSet(strSql, strTb);

            mp.ID = Convert.ToInt32(ds.Tables[0].Rows[0]["ID"]);
            mp.authorID = Convert.ToString(ds.Tables[0].Rows[0]["authorID"]);
            mp.publishTime = Convert.ToDateTime(ds.Tables[0].Rows[0]["publishTime"]);
            mp.content = Convert.ToString(ds.Tables[0].Rows[0]["content"]);

            return mp;
        }
    }
    /// <summary>
    /// 投票类
    /// </summary>
    public class Vote
    {
        public bool NewVote(VotePieceQuestion vpq, List<VotePieceChoice> lvpc)
        {
            string strTb = "VotePieceQuestion";
            if (vpq.startTime<=DateTime.Now&&vpq.endTime>=DateTime.Now)
            {
                vpq.isActive = true;
            }
            else vpq.isActive=false;
            string strSql = "insert into "+strTb+" (questionName,startTime,endTime,isActive,choiceNum) values('"
                +vpq.questionName+"',#"+vpq.startTime+"#,#"+vpq.endTime+"#,"+vpq.isActive.ToString()+","+vpq.choiceNum.ToString()+")";
            for (int i=0;i<vpq.choiceNum;i++)
            {
                AddVoteChoice(lvpc[i]);
            }
            return CC.CommonDataClass.UseQueryCommand(strSql);
        }
        public bool AddVoteChoice(VotePieceChoice vpc)
        {
            string strTb = "VotePieceChoice";
            string strSql = "insert into "+strTb+" (questionID,choiceContent) values("+vpc.questionID.ToString()+",'"+vpc.choiceContent+"')";
            return CC.CommonDataClass.UseQueryCommand(strSql);
        }
        public bool Voting(long vpcID)
        {
            string strTb1 = "VotePieceChoice";
            string strTb2 = "VotePieceQuestion";
            VotePieceChoice vpc = new VotePieceChoice();
            VotePieceQuestion vpq = new VotePieceQuestion();
            if (vpq.isActive == true)
            {
                string strSql1 = "select " + strTb1 + ".questionID," + strTb1 + ".choiceVoteNum," + strTb2 + ".questionID,"
                    + strTb2 + ".questionVoteNum from " + strTb2 + " inner join " + strTb1 + " on (" + strTb1 + ".questionID = " + strTb2 + ".questionID)";
                DataSet ds1 = CC.CommonDataClass.GetQueryDataSet(strSql1, strTb1);
                vpc.choiceVoteNum = Convert.ToInt32(ds1.Tables[0].Rows[0]["choiceVoteNum"]) + 1;
                vpq.questionVoteNum = Convert.ToInt32(ds1.Tables[0].Rows[0]["questionVoteNum"]) + 1;
                vpq.questionID = Convert.ToInt64(ds1.Tables[0].Rows[0]["questionID"]);
                string strSql2 = "update " + strTb1 + "," + strTb2 + " set " + strTb1 + ".choiceVoteNum=" + vpc.choiceVoteNum.ToString() + ","
                    + strTb2 + ".questionVoteNum=" + vpq.questionVoteNum.ToString() + " where " + strTb1 + ".choiceID=" + vpcID.ToString() + " and "
                    + strTb2 + ".questionID=" + vpq.questionID.ToString();
                return CC.CommonDataClass.UseQueryCommand(strSql2);
            }
            else return false;
        }
        public VotePieceQuestion GetVoteQuestion(long qID)
        {
            string strTb = "VotePieceQuestion";
            VotePieceQuestion vpq = new VotePieceQuestion();
            string strSql = "select * from " + strTb + " where questionID=" + qID.ToString();
            DataSet ds = CC.CommonDataClass.GetQueryDataSet(strSql,strTb);

            vpq.questionID = Convert.ToInt64(ds.Tables[0].Rows[0]["questionID"]);
            vpq.questionName = Convert.ToString(ds.Tables[0].Rows[0]["questionName"]);
            vpq.questionVoteNum = Convert.ToInt32(ds.Tables[0].Rows[0]["questionVoteNum"]);
            vpq.startTime = Convert.ToDateTime(ds.Tables[0].Rows[0]["startTime"]);
            vpq.endTime = Convert.ToDateTime(ds.Tables[0].Rows[0]["endTime"]);
            vpq.isActive = Convert.ToBoolean(ds.Tables[0].Rows[0]["isActive"]);

            return vpq;
        }
        public List<VotePieceChoice> GetVoteChoice(long qID)
        {
            string strTb = "VotePieceChoice";
            List<VotePieceChoice> lvpc = new List<VotePieceChoice>();
            string strSql = "select * from " + strTb + " where questionID=" + qID.ToString();
            DataSet ds = CC.CommonDataClass.GetQueryDataSet(strSql, strTb);
            int num = ds.Tables[0].Rows.Count;
            VotePieceChoice vpc = new VotePieceChoice();
            for (int i = 0; i < num; i++)
            {
                vpc.choiceContent = Convert.ToString(ds.Tables[0].Rows[i]["choiceContent"]);
                vpc.choiceID = Convert.ToInt64(ds.Tables[0].Rows[i]["choiceID"]);
                vpc.choiceVoteNum = Convert.ToInt32(ds.Tables[0].Rows[i]["choiceVoteNum"]);
                vpc.questionID = qID;
                lvpc.Add(vpc);
            }
            return lvpc;
        }
        public bool DeleteVote(long qID)
        {
            string strTb1 = "VotePieceQuestion";
            string strTb2 = "VotePieceChoice";
            string strSql1 = "delete from " + strTb1 + " where questionID=" + qID.ToString();
            string strSql2 = "delete from " + strTb2 + " where questionID=" + qID.ToString();
            return CC.CommonDataClass.UseQueryCommand(strSql1) && CC.CommonDataClass.UseQueryCommand(strSql2);
        }
        public bool DeleteVoteChoice(long cID)
        {
            string strTb = "VotePieceChoice";
            string strSql = "delete from " + strTb + " where choiceID=" + cID.ToString();
            return CC.CommonDataClass.UseQueryCommand(strSql);
        }
        public bool EditVoteChoice(VotePieceChoice vpc, long cID)
        {
            string strTb = "VotePieceChoice";
            string strSql = "update " + strTb + " set choiceContent='" + vpc.choiceContent + "' where choiceID=" + cID.ToString();
            return CC.CommonDataClass.UseQueryCommand(strSql);
        }
        public bool EditVote(VotePieceQuestion vpq, long qID)
        {
            string strTb = "VotePieceQuestion";
            string strSql = "update " + strTb + " set questionName='" + vpq.questionName + "',startTime=#" + vpq.startTime + "#,endTime=#"
                + vpq.endTime + "#,choiceNum=" + vpq.choiceNum + " where questionID=" + vpq.questionID.ToString();
            return CC.CommonDataClass.UseQueryCommand(strSql);
        }
    }
}